libname data "F:\CVS Projects\Macro\Final Results for Final Version of Paper\SAS and Stata Data";
libname data "G:\Macro\Final Results for Final Version of Paper\SAS and Stata Data";


*********************************************************
Files Used

data.raw_changes 
data.products
data.weekly_store_sales
*********************************************************;


* Create Variables;
data data.changes;
set  data.raw_changes;

if current_cost gt prior_cost then cost_increase = 1; else cost_increase=0;
if current_retail gt prior_retail then retail_increase = 1; else retail_increase=0;
if current_cost lt prior_cost then cost_decrease = 1; else cost_decrease=0;
if current_retail lt prior_retail then retail_decrease = 1; else retail_decrease=0;

cost_change =  current_cost - prior_cost;
retail_change =  current_retail - prior_retail;
priormargin =(prior_retail - prior_cost)/prior_retail;
log_priorunits = log(REG_units_Last_12_Mos);
log_priorrev   = log(REG_sales_Last_12_Mos);

if round(100*(prior_retail-floor(prior_retail)))=99 then prior_retail_99ending=1; else prior_retail_99ending=0;

if year=2005 then year2005 = 1; else year2005 = 0;
if year=2006 then year2006 = 1; else year2006 = 0;
if year=2007 then year2007 = 1; else year2007 = 0;
if year=2008 then year2008 = 1; else year2008 = 0;
if year=2009 then year2009 = 1; else year2009 = 0;

if month=1 then month1 = 1; else month1 = 0;
if month=2 then month2 = 1; else month2 = 0;
if month=3 then month3 = 1; else month3 = 0;
if month=4 then month4 = 1; else month4 = 0;
if month=5 then month5 = 1; else month5 = 0;
if month=6 then month6 = 1; else month6 = 0;
if month=7 then month7 = 1; else month7 = 0;
if month=8 then month8 = 1; else month8 = 0;
if month=9 then month9 = 1; else month9 = 0;
if month=10 then month10 = 1; else month10 = 0;
if month=11 then month11 = 1; else month11 = 0;
if month=12 then month12 = 1; else month12 = 0;
run;

* Omit obs with multiple obs in a month;
/*
proc means data = data.changes noprint nway;
output out=multiple
n (obs) = n_lineitems
max (cost_increase cost_decrease) = any_cost_increase any_cost_decrease
sum (cost_increase cost_decrease REG_SALES_Last_12_Mos REG_UNITS_Last_12_Mos) = sum_cost_increase sum_cost_decrease
sum_REG_SALES_Last_12_Mos sum_REG_UNITS_Last_12_Mos;
class primary_sku yearmonth last_cost_change;
run;
data multiple;
set multiple;
multiple_cost_changes =0;
if any_cost_increase + any_cost_decrease gt 1 then multiple_cost_changes =1;
if sum_cost_increase + sum_cost_decrease lt n_lineitems then multiple_cost_changes =1;
if any_cost_increase + any_cost_decrease = 0  then multiple_cost_changes =0;
drop _type_ _freq_; 
run;
proc sort data = multiple; by primary_sku yearmonth last_cost_change;run;
proc sort data = data.changes; by primary_sku yearmonth last_cost_change;run;
data data.changes3;
merge data.changes3 multiple;
by  primary_sku yearmonth last_cost_change;
if obs ne .;
run;
* Bring in random number and Order_for_multiples;
proc sort data = data.changes3; by obs; run;
data data.changes3;
merge data.changes3 work.random work.order_for_multiples;
by obs;
if order_for_multiples gt 1 then omit=1; else omit=0;
run;
*/
proc freq data = changes; tables cost_increase*omit cost_decrease*omit;where numberofskus_jul2010 ne .; run;
data data.changes;
set  data.changes;
if omit ne 1;
run;


* Calculate Nbr of SKUs;
data products;
set data.products;
if discon_ind = "N";
if first_scan_date le '07jul2010'd;
run;
proc means data = products noprint nway;
output out=numberofskus_jul2010
n (primary_sku) = numberofskus_jul2010;
class primary_sku;
where primary_sku ne . and primary_sku ne 0 ;
run;
proc sort data = data.changes;by primary_sku;run;
data data.changes;
merge data.changes numberofskus_jul2010;
by primary_sku;
if year ne .;
drop _type_ _freq_;
log_numberofskus_jul2010 = log(numberofskus_jul2010);
sku_units = REG_units_Last_12_Mos/numberofskus_jul2010;
log_sku_units = log(sku_units);
run;

* Calculate Segment Size;
proc means data=data.products noprint nway; * identify which category each primary_sku is in;
output out = products
n (sku_nbr) = whatever;
class primary_sku cat_nbr subcat_nbr seg_nbr;
where primary_sku ne . and primary_sku ne 0;
run;
proc means data = data.weekly_store_sales noprint nway; * restrict to items with sales in last year of the data; 
output out=soldskus
sum (scan_qty)=scan_qty;
class primary_sku;
where extnd_scan_amt gt 0 and scan_qty gt 0 and week_nbr gt 200845 and week_nbr lt 200945;  
run;
proc sort data = products; by primary_sku; run;
data soldskus2;
merge products soldskus;
by primary_sku;
if primary_sku ne .;
if primary_sku ne 0;
if scan_qty gt 0;
drop _type_ _freq_ whatever;
run;
proc means data = soldskus2 noprint nway; * calculate number of primary_skus in each category;
output out=seg_size
n (scan_qty )=seg_size ;
class cat_nbr subcat_nbr seg_nbr ;
run;
proc sort data = products; by cat_nbr subcat_nbr seg_nbr ; run;
data seg_size;
merge seg_size products;
by cat_nbr subcat_nbr seg_nbr ;
drop _type_ _freq_ whatever cat_nbr subcat_nbr seg_nbr;
run;

proc sort data = seg_size; by primary_sku; run;
data data.changes;
merge data.changes seg_size;
by primary_sku;
if year ne .;
seg_size_00 = seg_size/100;  * measure in 00's;
run;


*********************************************************
Table 1 Summary Statistics
*********************************************************;
proc means data=data.changes sum mean n;
var  retail_increase retail_decrease;
where numberofskus_jul2010 ne . and cost_increase=1;
run;
proc means data=data.changes sum mean n;
var  retail_increase retail_decrease;
where numberofskus_jul2010 ne . and cost_decrease=1;
run;


*********************************************************
Table 2 Size of Cost and Price Changes
*********************************************************;
data retail_changes;
set data.changes;
keep retail_change prior_retail;
if retail_increase=1 or retail_decrease=1;
if numberofskus_jul2010 ne .;
run;
data cost_changes;
set data.changes;
keep cost_change prior_retail;
if cost_increase=1 or cost_decrease=1;
if numberofskus_jul2010 ne .;
run;


*********************************************************
Table 3 Frequency Distribution of Nbr of SKUs;
*********************************************************;
proc means data = data.changes noprint nway;
output out = temp
mean (numberofskus_jul2010 reg_units_last_12_mos reg_sales_last_12_mos) = numberofskus reg_units_last_12_mos reg_sales_last_12_mos;
class primary_sku;
run;
data temp;
set temp;
drop _type_ _freq_;
reg_units_last_12_mos2 = reg_units_last_12_mos/100000;
reg_sales_last_12_mos2 = reg_sales_last_12_mos/100000;
run;
proc means data =  temp sum;
class numberofskus;
var reg_sales_last_12_mos2;  * revenue weighted;
where reg_sales_last_12_mos gt 0 and reg_units_last_12_mos gt 0;
run;
proc means data =  temp sum;
class numberofskus;
var reg_units_last_12_mos2;  * units weighted;
where reg_sales_last_12_mos gt 0 and reg_units_last_12_mos gt 0;
run;


*********************************************************
Figure 1 and 2 Nbr Variants and Prob. of a Price Change
Table 4: Freq. of Price Increases on Items with Mult. Variants
*********************************************************;
data temp;
set data.changes;
if numberofskus_jul2010 = 1 then singlesku_jul2010=1; else singlesku_jul2010=0;
if numberofskus_jul2010 = 2 then number2_jul2010=1; else number2_jul2010=0;
if numberofskus_jul2010 = 3 then number3_jul2010=1; else number3_jul2010=0;
if numberofskus_jul2010 = 4 then number4_jul2010=1; else number4_jul2010=0;
if numberofskus_jul2010 = 5 then number5_jul2010=1; else number5_jul2010=0;
if numberofskus_jul2010 = 6 then number6_jul2010=1; else number6_jul2010=0;
if numberofskus_jul2010 ge 7 then number7p_jul2010=1; else number7p_jul2010=0;
number2_3_jul2010 = number2_jul2010+number3_jul2010;
number4_6_jul2010 = number4_jul2010+number5_jul2010+number6_jul2010;
group4 = singlesku_jul2010 + 2*number2_3_jul2010 + 3*number4_6_jul2010 + 4*number7p_jul2010;
run;
proc means data=temp mean stderr;
var retail_increase;
class group4;
where cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;
proc means data=temp mean stderr;
var retail_increase;
class group4;
where cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
weight reg_sales_last_12_mos;
run;
proc means data=temp mean stderr;
var numberofskus_jul2010;
class group4;
where cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;
proc means data=temp mean stderr n;
var retail_increase;
where group4 ne . and cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;

*Table 4;
proc means data = temp mean sum n;
var retail_increase;
where singlesku_jul2010=1 and cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;
proc means data = temp sum ;
var numberofskus_jul2010;
where singlesku_jul2010=0 and cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;
proc means data = temp sum ;
var numberofskus_jul2010;
where retail_increase=1 and singlesku_jul2010=0 and cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;

proc means data = temp mean sum n;
var retail_increase;
where singlesku_jul2010=1 and cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
weight reg_sales_last_12_mos;
run;



*********************************************************
* Table 5 Factors that Contribute to the Decision to Raise the Price
*********************************************************;

**see Stata analysis;
**the code below creates the Stata data file;

data data.stata_logistic_final;
set data.changes;
if numberofskus_jul2010 ne .;
if reg_sales_last_12_mos ge 1;
if log_priorunits ne . ;

if cost_decrease = 1 then pct_cost_decrease=-pct_cost_change;
if cost_decrease = 1 then log_pct_cost_change = log(pct_cost_decrease);

yearmonth = year*100+month;
if numberofskus_jul2010 = 1 then singlesku_jul2010=1; else singlesku_jul2010=0;
if numberofskus_jul2010 = 2 then number2_jul2010=1; else number2_jul2010=0;
if numberofskus_jul2010 = 3 then number3_jul2010=1; else number3_jul2010=0;
if numberofskus_jul2010 = 4 then number4_jul2010=1; else number4_jul2010=0;
if numberofskus_jul2010 = 5 then number5_jul2010=1; else number5_jul2010=0;
if numberofskus_jul2010 = 6 then number6_jul2010=1; else number6_jul2010=0;
if numberofskus_jul2010 ge 7 then number7p_jul2010=1; else number7p_jul2010=0;
number2_3_jul2010 = number2_jul2010+number3_jul2010;
number4_6_jul2010 = number4_jul2010+number5_jul2010+number6_jul2010;
run;
 
* Add nbr_cost_increases and nbr_cost_decreases;
proc means data = data.stata_logistic_final noprint nway; 
output out = nbr_cost_decreases
n (primary_sku) = nbr_cost_decreases
mean (pct_cost_change) = mean_pct_cost_increase;
where cost_decrease =1;
class primary_sku;
run;
proc means data = data.stata_logistic_final noprint nway;
output out = nbr_cost_increases
n (primary_sku) = nbr_cost_increases
mean (pct_cost_change) = mean_pct_cost_increase;
where cost_increase =1;
class primary_sku;
run;

* Add private label;
data products;
set data.products;
if first_scan_date= . then first_scan_date = '01jan2003'd;
if prvt_label_ind = "Y" then private_label_item = 1;
if prvt_label_ind = "N" then private_label_item = 0;
keep primary_sku discon_dt discon_ind first_scan_date private_label_item;
run;
proc means data = products noprint nway;
output out = products
min (first_scan_date) = first_scan_date
max (discon_dt private_label_item) = discon_dt private_label_item;
class primary_sku;
run;

* Merge product information;
data nbr_cost_changes;
merge nbr_cost_increases nbr_cost_decreases products;
by primary_sku;
if nbr_cost_decreases ne . or nbr_cost_increases ne .;
if nbr_cost_decreases = . then nbr_cost_decreases = 0;
if nbr_cost_increases = . then nbr_cost_increases = 0;
nbr_cost_changes = nbr_cost_increases+ nbr_cost_decreases;
drop _type_ _freq_;
if first_scan_date le '01mar2005'd and discon_dt gt '01oct2009'd then duration = ('31oct2009'd - '01mar2005'd) / 365;
if first_scan_date le '01mar2005'd and discon_dt le '01oct2009'd then duration = (discon_dt - '01mar2005'd) / 365;
if first_scan_date gt '01mar2005'd and discon_dt gt '01oct2009'd then duration = ('31oct2009'd - first_scan_date) / 365;
if first_scan_date gt '01mar2005'd and discon_dt le '01oct2009'd then duration = (discon_dt - first_scan_date) / 365;
annual_cost_decreases = nbr_cost_decreases/duration;
annual_cost_increases = nbr_cost_increases/duration;
annual_cost_changes = nbr_cost_changes/duration;
run;

* Merge into Stata dataset;
proc sort data =  data.stata_logistic_final; by primary_sku ; run;
data data.stata_logistic_final;
merge  data.stata_logistic_final nbr_cost_changes;
by primary_sku;
run;


*********************************************************
Figure 3: Large vs. Small cost changes
*********************************************************;
proc freq data = data.changes;
tables pct_cost_change;
where cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;

data temp;
set data.changes;
if pct_cost_change ge  0.0598214286 then pct_cost_change_large=1;
if pct_cost_change lt  0.0598214286 then pct_cost_change_large=0;
if numberofskus_jul2010 = 1 then singlesku_jul2010=1; else singlesku_jul2010=0;
if numberofskus_jul2010 = 2 then number2_jul2010=1; else number2_jul2010=0;
if numberofskus_jul2010 = 3 then number3_jul2010=1; else number3_jul2010=0;
if numberofskus_jul2010 = 4 then number4_jul2010=1; else number4_jul2010=0;
if numberofskus_jul2010 = 5 then number5_jul2010=1; else number5_jul2010=0;
if numberofskus_jul2010 = 6 then number6_jul2010=1; else number6_jul2010=0;
if numberofskus_jul2010 ge 7 then number7p_jul2010=1; else number7p_jul2010=0;
number2_3_jul2010 = number2_jul2010+number3_jul2010;
number4_6_jul2010 = number4_jul2010+number5_jul2010+number6_jul2010;
group4 = singlesku_jul2010 + 2*number2_3_jul2010 + 3*number4_6_jul2010 + 4*number7p_jul2010;
run;
proc means data=temp mean ;
var retail_increase;
class pct_cost_change_large group4 ;
where cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne . ;
run;
proc means data=temp mean ;
var retail_increase;
class pct_cost_change_large;
where cost_increase =1 and numberofskus_jul2010 ne . and reg_sales_last_12_mos ge 1 and log_priorunits ne .  ;
run;


*********************************************************
Table 6  % of Items with a Future Price Increase
*********************************************************;
data obs;
set data.changes;
if cost_increase=1;
keep primary_sku last_cost_change retail_increase cost_increase;
run;
data next_priceincreases;
set data.changes; 
if retail_increase=1;
next_price_increase=last_retail_change;
next_price_incr_size=pct_retail_change;
next_priormargin=priormargin;
keep primary_sku next_price_increase next_price_incr_size next_priormargin;
run;
proc means data=next_priceincreases noprint nway;
output out = next_priceincreases
mean (next_price_incr_size next_priormargin)=next_price_incr_size next_priormargin;
class primary_sku next_price_increase;
run;

* next price increase;
proc sql;
create table next_priceincreases2 as
select a.primary_sku, a.next_price_increase, a.next_price_incr_size, a.next_priormargin, b.last_cost_change, b.retail_increase 
from next_priceincreases a full join obs b
on (a.primary_sku = b.primary_sku);
quit;
data next_priceincreases2;
set next_priceincreases2;
days_next_price_incr = next_price_increase-last_cost_change;
if days_next_price_incr gt 0  ;
run;
proc means data = next_priceincreases2 noprint nway;
output out=next_priceincreases3
min (days_next_price_incr) = min_days_next_price_incr;
class primary_sku last_cost_change;
run;
proc sort data=next_priceincreases2;by primary_sku last_cost_change;run;
data next_priceincreases2;
merge next_priceincreases2 next_priceincreases3;
by primary_sku last_cost_change;
if days_next_price_incr = min_days_next_price_incr;
run;
proc sort data=obs;by primary_sku last_cost_change;run;
data next_priceincreases3;
merge obs next_priceincreases2 ;
by primary_sku last_cost_change;
if days_next_price_incr gt 30 or days_next_price_incr = . then z30=0;else z30 = 1;
if days_next_price_incr gt 90 or days_next_price_incr = . then z90=0;else z90 = 1;
if days_next_price_incr gt 180 or days_next_price_incr = . then z180=0;else z180=1;
if days_next_price_incr gt 270 or days_next_price_incr = . then z270=0;else z270=1;
if days_next_price_incr gt 360 or days_next_price_incr = . then z360=0;else z360=1;
if days_next_price_incr gt 720 or days_next_price_incr = . then z720=0;else z720=1;
days_from_end_of_data=18170-last_cost_change;
if days_from_end_of_data lt 30 then z30 = .;
if days_from_end_of_data lt 90 then z90 = .;
if days_from_end_of_data lt 180 then z180 = .;
if days_from_end_of_data lt 270 then z270 = .;
if days_from_end_of_data lt 360 then z360 = .;
if days_from_end_of_data lt 720 then z720 = .;
run;

data alive; * Not Discontinued;
set data.products;
if discon_ind="N" then alive=1; 
first_scan = datepart(first_scan_dt);
if first_scan = . then first_scan = 16496;
keep primary_sku sku_nbr first_scan discon_dt;
run;
proc means data=alive noprint nway;
output out=alive
max (discon_dt)=discon_dt
min (first_scan)=first_scan;
class primary_sku;
run;
data next_priceincreases3;
merge  next_priceincreases3 alive;
by primary_sku;
drop _type_ _freq_;
if retail_increase ne .;
days_from_discon_dt=discon_dt-last_cost_change;
if days_from_discon_dt lt 30 then z30 = .;
if days_from_discon_dt lt 90 then z90 = .;
if days_from_discon_dt lt 180 then z180 = .;
if days_from_discon_dt lt 360 then z360 = .;
run;

proc means data = next_priceincreases3 mean stderr n;
var z30 z90 z180 z360;
run;
proc means data = next_priceincreases3 mean stderr n;
var z30 z90 z180 z360;
class retail_increase;
run;
proc ttest data = next_priceincreases3 ;
var z30 z90 z180 z360;
class retail_increase;
run;



* Prev Price Increase: discussed but not reported in paper;
proc sql;
create table next_priceincreases2 as
select a.primary_sku, a.next_price_increase, a.next_price_incr_size, a.next_priormargin, b.last_cost_change, b.retail_increase 
from next_priceincreases a full join obs b
on (a.primary_sku = b.primary_sku);
quit;
data prev_priceincreases2;
set next_priceincreases2;
days_prev_price_incr = last_cost_change-next_price_increase;
if days_prev_price_incr gt 0  ;
run;
proc means data = prev_priceincreases2 noprint nway;
output out=prev_priceincreases3
min (days_prev_price_incr) = min_days_prev_price_incr;
class primary_sku last_cost_change;
run;
proc sort data=prev_priceincreases2;by primary_sku last_cost_change;run;
data prev_priceincreases2;
merge prev_priceincreases2 prev_priceincreases3;
by primary_sku last_cost_change;
if days_prev_price_incr = min_days_prev_price_incr;
run;
proc sort data=obs;by primary_sku last_cost_change;run;
data prev_priceincreases3;
merge obs prev_priceincreases2 ;
by primary_sku last_cost_change;
if days_prev_price_incr gt 30 or days_prev_price_incr = . then p30=0;else p30 = 1;
if days_prev_price_incr gt 90 or days_prev_price_incr = . then p90=0;else p90 = 1;
if days_prev_price_incr gt 180 or days_prev_price_incr = . then p180=0;else p180=1;
if days_prev_price_incr gt 360 or days_prev_price_incr = . then p360=0;else p360=1;
days_from_start_of_data=last_cost_change-16496;
if days_from_start_of_data lt 30 then p30 = .;
if days_from_start_of_data lt 90 then p90 = .;
if days_from_start_of_data lt 180 then p180 = .;
if days_from_start_of_data lt 360 then p360 = .;
run;

data prev_priceincreases3; * Not Discontinued;
merge  prev_priceincreases3 alive;
by primary_sku;
drop _type_ _freq_;
if retail_increase ne .;
days_from_first_scan=last_cost_change-first_scan;
if days_from_first_scan lt 30 then p30 = .;
run;
proc means data = prev_priceincreases3 mean stderr n;
var p30 ;
class retail_increase;
run;




********************************************************
Figure 4  Difference in Price and Profit Margin Indexes
********************************************************;

* Aggregate weekly sales across stores;
proc means data=data.weekly_store_sales noprint nway ;
output out = temp_trend
sum (scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt)=
     scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt;
class primary_sku week_nbr;
run;
data temp_trend;
set  temp_trend;
drop _type_ _freq_;
cost = extnd_cost_amt / scan_qty;
retail_price = extnd_retl_amt / scan_qty;
scan_price = extnd_scan_amt / scan_qty;
run;

* Reduce Transaction Data to Items With Cost Increases;
proc means data = data.changes noprint nway; 
output out = changes_in_time
max (cost_increase )=valid;
class primary_sku;
where cost_increase=1;
run;
data temp_trend;
merge temp_trend changes_in_time;
by primary_sku;
if week_nbr ne .;
if _type_ ne .;
drop _type_ _freq_;
run;

* Merge Changes With Transaction Data;
data temp_trend;
set temp_trend;
if week_nbr = 200601 then week =1;
if week_nbr = 200602 then week =2;
if week_nbr = 200603 then week =3;
if week_nbr = 200604 then week =4;
if week_nbr = 200605 then week =5;
if week_nbr = 200606 then week =6;
if week_nbr = 200607 then week =7;
if week_nbr = 200608 then week =8;
if week_nbr = 200609 then week =9;
if week_nbr = 200610 then week =10;
if week_nbr = 200611 then week =11;
if week_nbr = 200612 then week =12;
if week_nbr = 200613 then week =13;
if week_nbr = 200614 then week =14;
if week_nbr = 200615 then week =15;
if week_nbr = 200616 then week =16;
if week_nbr = 200617 then week =17;
if week_nbr = 200618 then week =18;
if week_nbr = 200619 then week =19;
if week_nbr = 200620 then week =20;
if week_nbr = 200621 then week =21;
if week_nbr = 200622 then week =22;
if week_nbr = 200623 then week =23;
if week_nbr = 200624 then week =24;
if week_nbr = 200625 then week =25;
if week_nbr = 200626 then week =26;
if week_nbr = 200627 then week =27;
if week_nbr = 200628 then week =28;
if week_nbr = 200629 then week =29;
if week_nbr = 200630 then week =30;
if week_nbr = 200631 then week =31;
if week_nbr = 200632 then week =32;
if week_nbr = 200633 then week =33;
if week_nbr = 200634 then week =34;
if week_nbr = 200635 then week =35;
if week_nbr = 200636 then week =36;
if week_nbr = 200637 then week =37;
if week_nbr = 200638 then week =38;
if week_nbr = 200639 then week =39;
if week_nbr = 200640 then week =40;
if week_nbr = 200641 then week =41;
if week_nbr = 200642 then week =42;
if week_nbr = 200643 then week =43;
if week_nbr = 200644 then week =44;
if week_nbr = 200645 then week =45;
if week_nbr = 200646 then week =46;
if week_nbr = 200647 then week =47;
if week_nbr = 200648 then week =48;
if week_nbr = 200649 then week =49;
if week_nbr = 200650 then week =50;
if week_nbr = 200651 then week =51;
if week_nbr = 200652 then week =52;
if week_nbr = 200701 then week =53;
if week_nbr = 200702 then week =54;
if week_nbr = 200703 then week =55;
if week_nbr = 200704 then week =56;
if week_nbr = 200705 then week =57;
if week_nbr = 200706 then week =58;
if week_nbr = 200707 then week =59;
if week_nbr = 200708 then week =60;
if week_nbr = 200709 then week =61;
if week_nbr = 200710 then week =62;
if week_nbr = 200711 then week =63;
if week_nbr = 200712 then week =64;
if week_nbr = 200713 then week =65;
if week_nbr = 200714 then week =66;
if week_nbr = 200715 then week =67;
if week_nbr = 200716 then week =68;
if week_nbr = 200717 then week =69;
if week_nbr = 200718 then week =70;
if week_nbr = 200719 then week =71;
if week_nbr = 200720 then week =72;
if week_nbr = 200721 then week =73;
if week_nbr = 200722 then week =74;
if week_nbr = 200723 then week =75;
if week_nbr = 200724 then week =76;
if week_nbr = 200725 then week =77;
if week_nbr = 200726 then week =78;
if week_nbr = 200727 then week =79;
if week_nbr = 200728 then week =80;
if week_nbr = 200729 then week =81;
if week_nbr = 200730 then week =82;
if week_nbr = 200731 then week =83;
if week_nbr = 200732 then week =84;
if week_nbr = 200733 then week =85;
if week_nbr = 200734 then week =86;
if week_nbr = 200735 then week =87;
if week_nbr = 200736 then week =88;
if week_nbr = 200737 then week =89;
if week_nbr = 200738 then week =90;
if week_nbr = 200739 then week =91;
if week_nbr = 200740 then week =92;
if week_nbr = 200741 then week =93;
if week_nbr = 200742 then week =94;
if week_nbr = 200743 then week =95;
if week_nbr = 200744 then week =96;
if week_nbr = 200745 then week =97;
if week_nbr = 200746 then week =98;
if week_nbr = 200747 then week =99;
if week_nbr = 200748 then week =100;
if week_nbr = 200749 then week =101;
if week_nbr = 200750 then week =102;
if week_nbr = 200751 then week =103;
if week_nbr = 200752 then week =104;
if week_nbr = 200801 then week =105;
if week_nbr = 200802 then week =106;
if week_nbr = 200803 then week =107;
if week_nbr = 200804 then week =108;
if week_nbr = 200805 then week =109;
if week_nbr = 200806 then week =110;
if week_nbr = 200807 then week =111;
if week_nbr = 200808 then week =112;
if week_nbr = 200809 then week =113;
if week_nbr = 200810 then week =114;
if week_nbr = 200811 then week =115;
if week_nbr = 200812 then week =116;
if week_nbr = 200813 then week =117;
if week_nbr = 200814 then week =118;
if week_nbr = 200815 then week =119;
if week_nbr = 200816 then week =120;
if week_nbr = 200817 then week =121;
if week_nbr = 200818 then week =122;
if week_nbr = 200819 then week =123;
if week_nbr = 200820 then week =124;
if week_nbr = 200821 then week =125;
if week_nbr = 200822 then week =126;
if week_nbr = 200823 then week =127;
if week_nbr = 200824 then week =128;
if week_nbr = 200825 then week =129;
if week_nbr = 200826 then week =130;
if week_nbr = 200827 then week =131;
if week_nbr = 200828 then week =132;
if week_nbr = 200829 then week =133;
if week_nbr = 200830 then week =134;
if week_nbr = 200831 then week =135;
if week_nbr = 200832 then week =136;
if week_nbr = 200833 then week =137;
if week_nbr = 200834 then week =138;
if week_nbr = 200835 then week =139;
if week_nbr = 200836 then week =140;
if week_nbr = 200837 then week =141;
if week_nbr = 200838 then week =142;
if week_nbr = 200839 then week =143;
if week_nbr = 200840 then week =144;
if week_nbr = 200841 then week =145;
if week_nbr = 200842 then week =146;
if week_nbr = 200843 then week =147;
if week_nbr = 200844 then week =148;
if week_nbr = 200845 then week =149;
if week_nbr = 200846 then week =150;
if week_nbr = 200847 then week =151;
if week_nbr = 200848 then week =152;
if week_nbr = 200849 then week =153;
if week_nbr = 200850 then week =154;
if week_nbr = 200851 then week =155;
if week_nbr = 200852 then week =156;
if week_nbr = 200901 then week =157;
if week_nbr = 200902 then week =158;
if week_nbr = 200903 then week =159;
if week_nbr = 200904 then week =160;
if week_nbr = 200905 then week =161;
if week_nbr = 200906 then week =162;
if week_nbr = 200907 then week =163;
if week_nbr = 200908 then week =164;
if week_nbr = 200909 then week =165;
if week_nbr = 200910 then week =166;
if week_nbr = 200911 then week =167;
if week_nbr = 200912 then week =168;
if week_nbr = 200913 then week =169;
if week_nbr = 200914 then week =170;
if week_nbr = 200915 then week =171;
if week_nbr = 200916 then week =172;
if week_nbr = 200917 then week =173;
if week_nbr = 200918 then week =174;
if week_nbr = 200919 then week =175;
if week_nbr = 200920 then week =176;
if week_nbr = 200921 then week =177;
if week_nbr = 200922 then week =178;
if week_nbr = 200923 then week =179;
if week_nbr = 200924 then week =180;
if week_nbr = 200925 then week =181;
if week_nbr = 200926 then week =182;
if week_nbr = 200927 then week =183;
if week_nbr = 200928 then week =184;
if week_nbr = 200929 then week =185;
if week_nbr = 200930 then week =186;
if week_nbr = 200931 then week =187;
if week_nbr = 200932 then week =188;
if week_nbr = 200933 then week =189;
if week_nbr = 200934 then week =190;
if week_nbr = 200935 then week =191;
if week_nbr = 200936 then week =192;
if week_nbr = 200937 then week =193;
if week_nbr = 200938 then week =194;
if week_nbr = 200939 then week =195;
if week_nbr = 200940 then week =196;
if week_nbr = 200941 then week =197;
if week_nbr = 200942 then week =198;
if week_nbr = 200943 then week =199;
if week_nbr = 200944 then week =200;
if week_nbr = 200945 then week =201;
run;

data temp_changes;
set data.changes;
if last_cost_change ge 16802 and last_cost_change le 17830;
if cost_increase=1;
week=(last_cost_change-16802)/7;
week=ceil(week);
keep primary_sku week last_cost_change cost_increase retail_increase  prior_retail;
run;

proc means data=temp_changes noprint nway;
output out = temp_changes
max (cost_increase retail_increase) = cost_increase retail_increase
min (last_cost_change) = last_cost_change;
class primary_sku week;
run;

proc sort data=temp_trend; by primary_sku week; run;
data temp_changes;
merge  temp_trend temp_changes;
by primary_sku week;
if scan_qty gt 0 and scan_qty ne .;
drop _type_ _freq_;
run;

* Check Whether There Were Sales in Prior and Subsequent 52 weeks;
proc sort data =temp_changes;by primary_sku ;run;
proc rank data = temp_changes  out=temp_changes ties=low ;
var week;
ranks week_rank;
by primary_sku ;
run;

proc sort data=temp_changes; by primary_sku week; run;
data ranks_after;
set temp_changes;
week=week-52;
week_rank_after=week_rank;
keep primary_sku  week_rank_after week ;
run;
data ranks_before;
set temp_changes;
week=week+52;
week_rank_before=week_rank;
keep primary_sku  week_rank_before week ;
run;
data temp_changes2;
merge temp_changes ranks_before ranks_after;
by primary_sku week;
if scan_qty ne .;
run;

data valid_changes;
set temp_changes2;
if week_rank_after-week_rank=52;
if week_rank_before-week_rank=-52;
run;

* Identify First Cost Increase;
proc means data = valid_changes noprint nway;
output out=first_cost_increase
min (week)=first_cost_increase;
where cost_increase=1;
class primary_sku;
run;
data temp_changes2;
merge temp_changes2 first_cost_increase ;
by primary_sku;
drop _type_ _freq_;
run;
data temp_changes3;
set temp_changes2;
week_increase = week-first_cost_increase;
if week_increase le 52 and week_increase ge -52 and first_cost_increase ne .;
margin = (retail_price-cost)/retail_price;
run;

* Establish Baseline Measures;
proc means data = temp_changes3 noprint nway;
output out = baseline_increase
mean (retail_price scan_price cost margin scan_qty) = base_retail_price_increase base_scan_price_increase base_cost_increase  base_margin_increase base_scan_qty_increase;
where week_increase lt 0 and week_increase ge -52 and week_increase ne .;
class primary_sku ;
run;
data temp_changes3;
merge temp_changes3 baseline_increase;
by primary_sku;
if week_increase ge 0 and week_increase le 52 then index_margin_increase=margin/base_margin_increase-1;
if week_increase ge 0 and week_increase le 52 then index_retail_price_increase=retail_price/base_retail_price_increase-1;
if week_increase ge 0 and week_increase le 52 then index_cost_increase=cost/base_cost_increase-1;
if week_increase ge 0 and week_increase le 52 then index_scan_price_increase=scan_price/base_scan_price_increase-1;
if week_increase ge 0 and week_increase le 52 then index_scan_qty_increase=scan_qty/base_scan_qty_increase-1;
drop _type_ _freq_;
run;

* Identify retail_increase decision;
proc means data = temp_changes3 noprint nway;
output out = retail_decision
max (retail_increase)=retail_increase_decision;
class primary_sku;
where week_increase = 0 ;
run;
data trends_52weeksafter;
merge temp_changes3 retail_decision;
by primary_sku;
drop _type_ _freq_;
run;

* Analysis;
proc means data = trends_52weeksafter noprint nway;
output out = cost_increase_trend
mean (index_margin_increase index_cost_increase index_retail_price_increase index_scan_price_increase index_scan_qty_increase)=index_margin index_cost index_retail_price index_scan_price index_scan_qty_increase;
class retail_increase_decision week_increase;
where week_increase ge 0 and week_increase le 52;
run;



*************************************************
Table 7 Outcomes in Next 52 weeks
*************************************************;

* Identify Transactions;
data trend1;
set data.weekly_store_sales;
if scan_qty gt 0 and scan_qty ne .;
if week_nbr = 200601 then week =1;
if week_nbr = 200602 then week =2;
if week_nbr = 200603 then week =3;
if week_nbr = 200604 then week =4;
if week_nbr = 200605 then week =5;
if week_nbr = 200606 then week =6;
if week_nbr = 200607 then week =7;
if week_nbr = 200608 then week =8;
if week_nbr = 200609 then week =9;
if week_nbr = 200610 then week =10;
if week_nbr = 200611 then week =11;
if week_nbr = 200612 then week =12;
if week_nbr = 200613 then week =13;
if week_nbr = 200614 then week =14;
if week_nbr = 200615 then week =15;
if week_nbr = 200616 then week =16;
if week_nbr = 200617 then week =17;
if week_nbr = 200618 then week =18;
if week_nbr = 200619 then week =19;
if week_nbr = 200620 then week =20;
if week_nbr = 200621 then week =21;
if week_nbr = 200622 then week =22;
if week_nbr = 200623 then week =23;
if week_nbr = 200624 then week =24;
if week_nbr = 200625 then week =25;
if week_nbr = 200626 then week =26;
if week_nbr = 200627 then week =27;
if week_nbr = 200628 then week =28;
if week_nbr = 200629 then week =29;
if week_nbr = 200630 then week =30;
if week_nbr = 200631 then week =31;
if week_nbr = 200632 then week =32;
if week_nbr = 200633 then week =33;
if week_nbr = 200634 then week =34;
if week_nbr = 200635 then week =35;
if week_nbr = 200636 then week =36;
if week_nbr = 200637 then week =37;
if week_nbr = 200638 then week =38;
if week_nbr = 200639 then week =39;
if week_nbr = 200640 then week =40;
if week_nbr = 200641 then week =41;
if week_nbr = 200642 then week =42;
if week_nbr = 200643 then week =43;
if week_nbr = 200644 then week =44;
if week_nbr = 200645 then week =45;
if week_nbr = 200646 then week =46;
if week_nbr = 200647 then week =47;
if week_nbr = 200648 then week =48;
if week_nbr = 200649 then week =49;
if week_nbr = 200650 then week =50;
if week_nbr = 200651 then week =51;
if week_nbr = 200652 then week =52;
if week_nbr = 200701 then week =53;
if week_nbr = 200702 then week =54;
if week_nbr = 200703 then week =55;
if week_nbr = 200704 then week =56;
if week_nbr = 200705 then week =57;
if week_nbr = 200706 then week =58;
if week_nbr = 200707 then week =59;
if week_nbr = 200708 then week =60;
if week_nbr = 200709 then week =61;
if week_nbr = 200710 then week =62;
if week_nbr = 200711 then week =63;
if week_nbr = 200712 then week =64;
if week_nbr = 200713 then week =65;
if week_nbr = 200714 then week =66;
if week_nbr = 200715 then week =67;
if week_nbr = 200716 then week =68;
if week_nbr = 200717 then week =69;
if week_nbr = 200718 then week =70;
if week_nbr = 200719 then week =71;
if week_nbr = 200720 then week =72;
if week_nbr = 200721 then week =73;
if week_nbr = 200722 then week =74;
if week_nbr = 200723 then week =75;
if week_nbr = 200724 then week =76;
if week_nbr = 200725 then week =77;
if week_nbr = 200726 then week =78;
if week_nbr = 200727 then week =79;
if week_nbr = 200728 then week =80;
if week_nbr = 200729 then week =81;
if week_nbr = 200730 then week =82;
if week_nbr = 200731 then week =83;
if week_nbr = 200732 then week =84;
if week_nbr = 200733 then week =85;
if week_nbr = 200734 then week =86;
if week_nbr = 200735 then week =87;
if week_nbr = 200736 then week =88;
if week_nbr = 200737 then week =89;
if week_nbr = 200738 then week =90;
if week_nbr = 200739 then week =91;
if week_nbr = 200740 then week =92;
if week_nbr = 200741 then week =93;
if week_nbr = 200742 then week =94;
if week_nbr = 200743 then week =95;
if week_nbr = 200744 then week =96;
if week_nbr = 200745 then week =97;
if week_nbr = 200746 then week =98;
if week_nbr = 200747 then week =99;
if week_nbr = 200748 then week =100;
if week_nbr = 200749 then week =101;
if week_nbr = 200750 then week =102;
if week_nbr = 200751 then week =103;
if week_nbr = 200752 then week =104;
if week_nbr = 200801 then week =105;
if week_nbr = 200802 then week =106;
if week_nbr = 200803 then week =107;
if week_nbr = 200804 then week =108;
if week_nbr = 200805 then week =109;
if week_nbr = 200806 then week =110;
if week_nbr = 200807 then week =111;
if week_nbr = 200808 then week =112;
if week_nbr = 200809 then week =113;
if week_nbr = 200810 then week =114;
if week_nbr = 200811 then week =115;
if week_nbr = 200812 then week =116;
if week_nbr = 200813 then week =117;
if week_nbr = 200814 then week =118;
if week_nbr = 200815 then week =119;
if week_nbr = 200816 then week =120;
if week_nbr = 200817 then week =121;
if week_nbr = 200818 then week =122;
if week_nbr = 200819 then week =123;
if week_nbr = 200820 then week =124;
if week_nbr = 200821 then week =125;
if week_nbr = 200822 then week =126;
if week_nbr = 200823 then week =127;
if week_nbr = 200824 then week =128;
if week_nbr = 200825 then week =129;
if week_nbr = 200826 then week =130;
if week_nbr = 200827 then week =131;
if week_nbr = 200828 then week =132;
if week_nbr = 200829 then week =133;
if week_nbr = 200830 then week =134;
if week_nbr = 200831 then week =135;
if week_nbr = 200832 then week =136;
if week_nbr = 200833 then week =137;
if week_nbr = 200834 then week =138;
if week_nbr = 200835 then week =139;
if week_nbr = 200836 then week =140;
if week_nbr = 200837 then week =141;
if week_nbr = 200838 then week =142;
if week_nbr = 200839 then week =143;
if week_nbr = 200840 then week =144;
if week_nbr = 200841 then week =145;
if week_nbr = 200842 then week =146;
if week_nbr = 200843 then week =147;
if week_nbr = 200844 then week =148;
if week_nbr = 200845 then week =149;
if week_nbr = 200846 then week =150;
if week_nbr = 200847 then week =151;
if week_nbr = 200848 then week =152;
if week_nbr = 200849 then week =153;
if week_nbr = 200850 then week =154;
if week_nbr = 200851 then week =155;
if week_nbr = 200852 then week =156;
if week_nbr = 200901 then week =157;
if week_nbr = 200902 then week =158;
if week_nbr = 200903 then week =159;
if week_nbr = 200904 then week =160;
if week_nbr = 200905 then week =161;
if week_nbr = 200906 then week =162;
if week_nbr = 200907 then week =163;
if week_nbr = 200908 then week =164;
if week_nbr = 200909 then week =165;
if week_nbr = 200910 then week =166;
if week_nbr = 200911 then week =167;
if week_nbr = 200912 then week =168;
if week_nbr = 200913 then week =169;
if week_nbr = 200914 then week =170;
if week_nbr = 200915 then week =171;
if week_nbr = 200916 then week =172;
if week_nbr = 200917 then week =173;
if week_nbr = 200918 then week =174;
if week_nbr = 200919 then week =175;
if week_nbr = 200920 then week =176;
if week_nbr = 200921 then week =177;
if week_nbr = 200922 then week =178;
if week_nbr = 200923 then week =179;
if week_nbr = 200924 then week =180;
if week_nbr = 200925 then week =181;
if week_nbr = 200926 then week =182;
if week_nbr = 200927 then week =183;
if week_nbr = 200928 then week =184;
if week_nbr = 200929 then week =185;
if week_nbr = 200930 then week =186;
if week_nbr = 200931 then week =187;
if week_nbr = 200932 then week =188;
if week_nbr = 200933 then week =189;
if week_nbr = 200934 then week =190;
if week_nbr = 200935 then week =191;
if week_nbr = 200936 then week =192;
if week_nbr = 200937 then week =193;
if week_nbr = 200938 then week =194;
if week_nbr = 200939 then week =195;
if week_nbr = 200940 then week =196;
if week_nbr = 200941 then week =197;
if week_nbr = 200942 then week =198;
if week_nbr = 200943 then week =199;
if week_nbr = 200944 then week =200;
if week_nbr = 200945 then week =201;
keep primary_sku store_nbr week retail_price cost scan_price scan_qty extnd_retl_amt extnd_cost_amt extnd_scan_amt;
run;

* Identify First and Last Week of Sales by Store x Item;
proc means data=trend1 noprint nway;
output out=itemsinstore
max (week) = last_week
min (week) = first_week;
class primary_sku store_nbr;
run;
proc sort data=trend1; by primary_sku store_nbr; run;
data trend1;
merge trend1 itemsinstore;
by primary_sku store_nbr;
weeksofstoreitemsales=_freq_ ;
drop _type_ _freq_;
run;

* Identify Cost Increases;
data temp_changes;
set data.changes;
if last_cost_change ge 16802 and last_cost_change le 17830;
if cost_increase=1;
week=(last_cost_change-16802)/7;
week=ceil(week);
keep primary_sku week last_cost_change cost_increase retail_increase prior_retail ;
run;
proc means data=temp_changes noprint nway;
output out = cost_increases
max (retail_increase prior_retail) = retail_increase prior_retail;
class primary_sku week;
run;
data cost_increases;
set cost_increases;
week_cost_increase=week;
drop _type_ _freq_;
run;
proc sort data=cost_increases; by primary_sku week; run;
proc sort data=trend1; by primary_sku week; run;
data trend1;
merge trend1 cost_increases;
by primary_sku week;
if scan_qty ne .;
run;

* Reduce to Items with cost increases;
proc means data=cost_increases noprint nway;
output out = items_with_cost_increases
max (week) = last_week;
class primary_sku ;
run;
data items_with_cost_increases;
set items_with_cost_increases;
keep =1;
keep primary_sku keep;
run;
data trend1a;
merge trend1 items_with_cost_increases;
by primary_sku;
if keep =1;
drop keep;
run;

* Select First Qualifying Cost Increase;
proc means data = trend1a noprint nway;
class primary_sku store_nbr;
output out = qualifying
min (week) = first_qualifying_event;
where retail_increase ne . and week-first_week ge 52 and last_week - week ge 52;
run;
proc sort data = trend1a; by primary_sku store_nbr; run;
data qualifying_weeks;
merge trend1a qualifying;
by primary_sku store_nbr;
if first_qualifying_event ne .;
drop _type_ _freq_;
run;

* Set Retail Decision;
proc means data = qualifying_weeks noprint nway;
class primary_sku store_nbr;
output out = retail_decision
min (retail_increase ) = retail_decision ;
where first_qualifying_event=week;
run;
data qualifying_weeks;
merge qualifying_weeks retail_decision;
by primary_sku store_nbr;
drop _type_ _freq_;
run;

* Baseline Measures;
proc means data = qualifying_weeks noprint nway;
output out = baselineperiod
sum (scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt) = base_scan_qty base_extnd_scan_amt base_extnd_retl_amt base_extnd_cost_amt
max (retail_decision first_qualifying_event) = retail_decision first_qualifying_event;
where week-first_qualifying_event ge -52 and week-first_qualifying_event lt 0 and week ne . and first_qualifying_event ne .;
class primary_sku store_nbr ;
run;
proc means data =baselineperiod; var base_scan_qty _freq_ ; run;

* Post Measures;
proc means data = qualifying_weeks noprint nway;
output out = postperiod
sum (scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt) = post_scan_qty post_extnd_scan_amt post_extnd_retl_amt post_extnd_cost_amt;
where week-first_qualifying_event le 52 and week-first_qualifying_event gt 0 and week ne . and first_qualifying_event ne .;
class primary_sku store_nbr ;
run;

* Aggregate to Primary_SKU Level;
proc means data = qualifying_weeks noprint nway;
output out = baselineperiod_prim
sum (scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt) = base_scan_qty base_extnd_scan_amt base_extnd_retl_amt base_extnd_cost_amt
max (retail_decision ) = retail_decision ;
where week-first_qualifying_event ge -52 and week-first_qualifying_event lt 0 and week ne . and first_qualifying_event ne .;
class primary_sku first_qualifying_event;
run;
proc means data = qualifying_weeks noprint nway;
output out = postperiod_prim
sum (scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt) = post_scan_qty post_extnd_scan_amt post_extnd_retl_amt post_extnd_cost_amt;
where week-first_qualifying_event le 52 and week-first_qualifying_event gt 0 and week ne . and first_qualifying_event ne .;
class primary_sku first_qualifying_event;
run;
proc means data =postperiod; var _freq_; run;
data scan_qty_prim;
merge postperiod_prim baselineperiod_prim;
by primary_sku first_qualifying_event;
scan_qty_index = (post_scan_qty) / (base_scan_qty) -1;
retail_price_index = (post_extnd_retl_amt/post_scan_qty) / (base_extnd_retl_amt/base_scan_qty)-1;
cost_index = (post_extnd_cost_amt/post_scan_qty) / (base_extnd_cost_amt/base_scan_qty)-1;
run;

proc means data = scan_qty_prim mean stderr;
var scan_qty_index retail_price_index cost_index;
class retail_decision;
weight base_scan_qty;
run;
proc ttest data = scan_qty_prim ;
var scan_qty_index retail_price_index cost_index;
class retail_decision;
weight base_scan_qty;
run;




***************************

Appendix

***************************;


*********************************************
Summary Statistics (Independent Variables)
*********************************************;
proc means data=data.changes mean stderr n;
var prior_retail_99ending pct_cost_change priormargin log_priorunits seg_size_00;
where cost_increase=1;
run;


**********************************************
Robustness Checks for Main Logistic Model
**********************************************;

* See Stata file;




*************************************************
Difference in Price and Profit Margin Indexes
Replicate for Cost Decreases
*************************************************;

* Aggregate weekly sales across stores;
proc means data=data.weekly_store_sales noprint nway ;
output out = temp_trend_dcr
sum (scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt)=
     scan_qty extnd_scan_amt extnd_retl_amt extnd_cost_amt;
class primary_sku week_nbr;
run;
data temp_trend_dcr;
set  temp_trend_dcr;
drop _type_ _freq_;
cost = extnd_cost_amt / scan_qty;
retail_price = extnd_retl_amt / scan_qty;
scan_price = extnd_scan_amt / scan_qty;
run;

*Reduce Transaction Data to Items With Cost Decreases;
data temp_trend_dcr;
set emi.qual_year06to09_prim ;
keep primary_sku week_nbr retail_price scan_price cost scan_qty;
run;
proc means data = data.changes noprint nway; 
output out = changes_in_time_dcr
max (cost_decrease )=valid;
class primary_sku;
where cost_decrease=1;
run;
data temp_trend_dcr;
merge temp_trend_dcr changes_in_time_dcr;
by primary_sku;
if week_nbr ne .;
if _type_ ne .;
drop _type_ _freq_;
run;

* Merge Changes With Transaction Data;
data temp_trend_dcr;
set temp_trend_dcr;
if week_nbr = 200601 then week =1;
if week_nbr = 200602 then week =2;
if week_nbr = 200603 then week =3;
if week_nbr = 200604 then week =4;
if week_nbr = 200605 then week =5;
if week_nbr = 200606 then week =6;
if week_nbr = 200607 then week =7;
if week_nbr = 200608 then week =8;
if week_nbr = 200609 then week =9;
if week_nbr = 200610 then week =10;
if week_nbr = 200611 then week =11;
if week_nbr = 200612 then week =12;
if week_nbr = 200613 then week =13;
if week_nbr = 200614 then week =14;
if week_nbr = 200615 then week =15;
if week_nbr = 200616 then week =16;
if week_nbr = 200617 then week =17;
if week_nbr = 200618 then week =18;
if week_nbr = 200619 then week =19;
if week_nbr = 200620 then week =20;
if week_nbr = 200621 then week =21;
if week_nbr = 200622 then week =22;
if week_nbr = 200623 then week =23;
if week_nbr = 200624 then week =24;
if week_nbr = 200625 then week =25;
if week_nbr = 200626 then week =26;
if week_nbr = 200627 then week =27;
if week_nbr = 200628 then week =28;
if week_nbr = 200629 then week =29;
if week_nbr = 200630 then week =30;
if week_nbr = 200631 then week =31;
if week_nbr = 200632 then week =32;
if week_nbr = 200633 then week =33;
if week_nbr = 200634 then week =34;
if week_nbr = 200635 then week =35;
if week_nbr = 200636 then week =36;
if week_nbr = 200637 then week =37;
if week_nbr = 200638 then week =38;
if week_nbr = 200639 then week =39;
if week_nbr = 200640 then week =40;
if week_nbr = 200641 then week =41;
if week_nbr = 200642 then week =42;
if week_nbr = 200643 then week =43;
if week_nbr = 200644 then week =44;
if week_nbr = 200645 then week =45;
if week_nbr = 200646 then week =46;
if week_nbr = 200647 then week =47;
if week_nbr = 200648 then week =48;
if week_nbr = 200649 then week =49;
if week_nbr = 200650 then week =50;
if week_nbr = 200651 then week =51;
if week_nbr = 200652 then week =52;
if week_nbr = 200701 then week =53;
if week_nbr = 200702 then week =54;
if week_nbr = 200703 then week =55;
if week_nbr = 200704 then week =56;
if week_nbr = 200705 then week =57;
if week_nbr = 200706 then week =58;
if week_nbr = 200707 then week =59;
if week_nbr = 200708 then week =60;
if week_nbr = 200709 then week =61;
if week_nbr = 200710 then week =62;
if week_nbr = 200711 then week =63;
if week_nbr = 200712 then week =64;
if week_nbr = 200713 then week =65;
if week_nbr = 200714 then week =66;
if week_nbr = 200715 then week =67;
if week_nbr = 200716 then week =68;
if week_nbr = 200717 then week =69;
if week_nbr = 200718 then week =70;
if week_nbr = 200719 then week =71;
if week_nbr = 200720 then week =72;
if week_nbr = 200721 then week =73;
if week_nbr = 200722 then week =74;
if week_nbr = 200723 then week =75;
if week_nbr = 200724 then week =76;
if week_nbr = 200725 then week =77;
if week_nbr = 200726 then week =78;
if week_nbr = 200727 then week =79;
if week_nbr = 200728 then week =80;
if week_nbr = 200729 then week =81;
if week_nbr = 200730 then week =82;
if week_nbr = 200731 then week =83;
if week_nbr = 200732 then week =84;
if week_nbr = 200733 then week =85;
if week_nbr = 200734 then week =86;
if week_nbr = 200735 then week =87;
if week_nbr = 200736 then week =88;
if week_nbr = 200737 then week =89;
if week_nbr = 200738 then week =90;
if week_nbr = 200739 then week =91;
if week_nbr = 200740 then week =92;
if week_nbr = 200741 then week =93;
if week_nbr = 200742 then week =94;
if week_nbr = 200743 then week =95;
if week_nbr = 200744 then week =96;
if week_nbr = 200745 then week =97;
if week_nbr = 200746 then week =98;
if week_nbr = 200747 then week =99;
if week_nbr = 200748 then week =100;
if week_nbr = 200749 then week =101;
if week_nbr = 200750 then week =102;
if week_nbr = 200751 then week =103;
if week_nbr = 200752 then week =104;
if week_nbr = 200801 then week =105;
if week_nbr = 200802 then week =106;
if week_nbr = 200803 then week =107;
if week_nbr = 200804 then week =108;
if week_nbr = 200805 then week =109;
if week_nbr = 200806 then week =110;
if week_nbr = 200807 then week =111;
if week_nbr = 200808 then week =112;
if week_nbr = 200809 then week =113;
if week_nbr = 200810 then week =114;
if week_nbr = 200811 then week =115;
if week_nbr = 200812 then week =116;
if week_nbr = 200813 then week =117;
if week_nbr = 200814 then week =118;
if week_nbr = 200815 then week =119;
if week_nbr = 200816 then week =120;
if week_nbr = 200817 then week =121;
if week_nbr = 200818 then week =122;
if week_nbr = 200819 then week =123;
if week_nbr = 200820 then week =124;
if week_nbr = 200821 then week =125;
if week_nbr = 200822 then week =126;
if week_nbr = 200823 then week =127;
if week_nbr = 200824 then week =128;
if week_nbr = 200825 then week =129;
if week_nbr = 200826 then week =130;
if week_nbr = 200827 then week =131;
if week_nbr = 200828 then week =132;
if week_nbr = 200829 then week =133;
if week_nbr = 200830 then week =134;
if week_nbr = 200831 then week =135;
if week_nbr = 200832 then week =136;
if week_nbr = 200833 then week =137;
if week_nbr = 200834 then week =138;
if week_nbr = 200835 then week =139;
if week_nbr = 200836 then week =140;
if week_nbr = 200837 then week =141;
if week_nbr = 200838 then week =142;
if week_nbr = 200839 then week =143;
if week_nbr = 200840 then week =144;
if week_nbr = 200841 then week =145;
if week_nbr = 200842 then week =146;
if week_nbr = 200843 then week =147;
if week_nbr = 200844 then week =148;
if week_nbr = 200845 then week =149;
if week_nbr = 200846 then week =150;
if week_nbr = 200847 then week =151;
if week_nbr = 200848 then week =152;
if week_nbr = 200849 then week =153;
if week_nbr = 200850 then week =154;
if week_nbr = 200851 then week =155;
if week_nbr = 200852 then week =156;
if week_nbr = 200901 then week =157;
if week_nbr = 200902 then week =158;
if week_nbr = 200903 then week =159;
if week_nbr = 200904 then week =160;
if week_nbr = 200905 then week =161;
if week_nbr = 200906 then week =162;
if week_nbr = 200907 then week =163;
if week_nbr = 200908 then week =164;
if week_nbr = 200909 then week =165;
if week_nbr = 200910 then week =166;
if week_nbr = 200911 then week =167;
if week_nbr = 200912 then week =168;
if week_nbr = 200913 then week =169;
if week_nbr = 200914 then week =170;
if week_nbr = 200915 then week =171;
if week_nbr = 200916 then week =172;
if week_nbr = 200917 then week =173;
if week_nbr = 200918 then week =174;
if week_nbr = 200919 then week =175;
if week_nbr = 200920 then week =176;
if week_nbr = 200921 then week =177;
if week_nbr = 200922 then week =178;
if week_nbr = 200923 then week =179;
if week_nbr = 200924 then week =180;
if week_nbr = 200925 then week =181;
if week_nbr = 200926 then week =182;
if week_nbr = 200927 then week =183;
if week_nbr = 200928 then week =184;
if week_nbr = 200929 then week =185;
if week_nbr = 200930 then week =186;
if week_nbr = 200931 then week =187;
if week_nbr = 200932 then week =188;
if week_nbr = 200933 then week =189;
if week_nbr = 200934 then week =190;
if week_nbr = 200935 then week =191;
if week_nbr = 200936 then week =192;
if week_nbr = 200937 then week =193;
if week_nbr = 200938 then week =194;
if week_nbr = 200939 then week =195;
if week_nbr = 200940 then week =196;
if week_nbr = 200941 then week =197;
if week_nbr = 200942 then week =198;
if week_nbr = 200943 then week =199;
if week_nbr = 200944 then week =200;
if week_nbr = 200945 then week =201;
run;

data temp_changes_dcr;
set data.changes;
if last_cost_change ge 16802 and last_cost_change le 17830;
if cost_decrease=1;
week=(last_cost_change-16802)/7;
week=ceil(week);
keep primary_sku week last_cost_change cost_decrease retail_decrease prior_retail ;
run;
proc means data=temp_changes_dcr noprint nway;
output out = temp_changes_dcr
max (cost_decrease retail_decrease) = cost_decrease retail_decrease
min (last_cost_change) = last_cost_change;
class primary_sku week;
run;
proc sort data=temp_trend_dcr; by primary_sku week; run;
data temp_changes_dcr;
merge  temp_trend_dcr temp_changes_dcr;
by primary_sku week;
if scan_qty gt 0 and scan_qty ne .;
drop _type_ _freq_;
run;

* Check Whether There Were Sales in Prior and Subsequent 52 weeks;
proc sort data =temp_changes_dcr;by primary_sku ;run;
proc rank data = temp_changes_dcr  out=temp_changes_dcr ties=low ;
var week;
ranks week_rank;
by primary_sku ;
run;
proc sort data=temp_changes_dcr; by primary_sku week; run;
data ranks_after_dcr;
set temp_changes_dcr;
week=week-52;
week_rank_after=week_rank;
keep primary_sku  week_rank_after week ;
run;
data ranks_before_dcr;
set temp_changes_dcr;
week=week+52;
week_rank_before=week_rank;
keep primary_sku  week_rank_before week ;
run;
data temp_changes2_dcr;
merge temp_changes_dcr ranks_before_dcr ranks_after_dcr;
by primary_sku week;
if scan_qty ne .;
run;
data valid_changes_dcr;
set temp_changes2_dcr;
if week_rank_after-week_rank=52;
if week_rank_before-week_rank=-52;
run;

* Identify First Cost Increase Cost Decrease Cost Change;
proc means data = valid_changes_dcr noprint nway;
output out=first_cost_decrease
min (week)=first_cost_decrease;
where cost_decrease=1;
class primary_sku;
run;
data temp_changes2_dcr;
merge temp_changes2_dcr first_cost_decrease ;
by primary_sku;
drop _type_ _freq_;
run;
data temp_changes3_dcr;
set temp_changes2_dcr;
week_decrease = week-first_cost_decrease;
if week_decrease le 52 and week_decrease ge -52 and first_cost_decrease ne .;
margin = (retail_price-cost)/retail_price;
run;

* Establish Baseline Measures;
proc means data = temp_changes3_dcr noprint nway;
output out = baseline_decrease
mean (retail_price scan_price cost margin scan_qty) = base_retail_price_decrease base_scan_price_decrease base_cost_decrease  base_margin_decrease base_scan_qty_decrease;
where week_decrease lt 0 and week_decrease ge -52 and week_decrease ne .;
class primary_sku ;
run;
data temp_changes3_dcr;
merge temp_changes3_dcr baseline_decrease;
by primary_sku;
if week_decrease ge 0 and week_decrease le 52 then index_margin_decrease=margin/base_margin_decrease-1;
if week_decrease ge 0 and week_decrease le 52 then index_retail_price_decrease=retail_price/base_retail_price_decrease-1;
if week_decrease ge 0 and week_decrease le 52 then index_cost_decrease=cost/base_cost_decrease-1;
if week_decrease ge 0 and week_decrease le 52 then index_scan_price_decrease=scan_price/base_scan_price_decrease-1;
if week_decrease ge 0 and week_decrease le 52 then index_scan_qty_decrease=scan_qty/base_scan_qty_decrease-1;
drop _type_ _freq_;
run;

* Identify retail_decrease decision;
proc means data = temp_changes3_dcr noprint nway;
output out = retail_decision_dcr
max (retail_decrease)=retail_decrease_decision;
class primary_sku;
where week_decrease = 0 ;
run;
data trends_52weeksafter_dcr;
merge temp_changes3_dcr retail_decision_dcr;
by primary_sku;
drop _type_ _freq_;
run;

* Analysis;
proc means data = trends_52weeksafter_dcr noprint nway;
output out = cost_decrease_trend
mean (index_margin_decrease index_cost_decrease index_retail_price_decrease index_scan_price_decrease index_scan_qty_decrease)=index_margin index_cost index_retail_price index_scan_price index_scan_qty_decrease;
class retail_decrease_decision week_decrease;
where week_decrease ge 0 and week_decrease le 52;
run;
